package dao;

import bean.Advice;
import util.DruidUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class AdviceDao {
    Connection conn=null;
    public List<Advice> getAdviceList(int replySubject, int pageNum){

        ArrayList<Advice> AdviceList =new ArrayList<Advice>();
        int k=(pageNum-1)*5;
        StringBuffer sql=new StringBuffer();
        sql.append("select * from egov_advice where   1=1 ");

            sql.append("  and  replySubject = ? ");

        sql.append(" limit "+k+",5");
        try {
            conn= DruidUtil.getConnection();
            PreparedStatement ps=conn.prepareStatement(sql.toString());
            int index=1;

            ps.setInt(index,replySubject);
            index++;

            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                Advice m=new Advice();
                m.setId(rs.getInt("id"));
                m.setReplySubject(rs.getInt("replySubject"));
                m.setContent(rs.getString("content"));
                m.setName(rs.getString("name"));
                m.setPhone(rs.getString("phone"));
                m.setEmail(rs.getString("email"));
                AdviceList.add(m);

            }
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return AdviceList;
    }
    public int getAdviceListCount(int replySubject){
        int count=0;
        StringBuffer sql=new StringBuffer();
        sql.append("select count(*) from egov_advice  where 1=1 ");

        sql.append(" and  replySubject = ? ");

        try {
            conn= DruidUtil.getConnection();
            PreparedStatement ps=conn.prepareStatement(sql.toString());
            int index=1;
            ps.setInt(index,replySubject);
            index++;

            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                count=rs.getInt(1);
            }
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  count;
    }
    public boolean addAdvice(Advice m){
        StringBuffer sb1= new StringBuffer("insert into egov_advice(");
        StringBuffer sb2=new StringBuffer(" values( ");
        List<Object> param = new ArrayList<Object>();


        sb1.append(",replySubject ");
        sb2.append(" ,?");
        param.add(Integer.valueOf(m.getReplySubject()));

        sb1.append(",content ");
        sb2.append(" ,?");
        param.add(m.getContent());

        sb1.append(",name ");
        sb2.append(" , ? ");
        param.add(m.getName());

        sb1.append(",phone ");
        sb2.append(", ?");
        param.add(m.getPhone());

        sb1.append(",email ");
        sb2.append(", ?");
        param.add(m.getEmail());

        sb1.append(")");
        sb2.append(")");
        String sql1 = sb1.toString().replaceFirst(",", " ");
        String sql2 = sb2.toString().replaceFirst(",", " ");

        int seq = 1;
        try {
            conn= DruidUtil.getConnection();
            PreparedStatement ps=conn.prepareStatement(sql1+sql2);
            for (Object elem : param) {
                if (elem instanceof String) {
                    ps.setString(seq, (String) elem);
                } else if (elem instanceof Integer) {
                    ps.setInt(seq, (Integer) elem);
                }
                seq++;
            }
            if (ps.execute()){
                conn.close();
                return true;
            }else {
                conn.close();
                return false;
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }

        return false;
    }

    public Advice getAdviceById(int id){
        Advice m=new Advice();
        String sql="select * from egov_advice where  id = ? ";
        try {
            conn= DruidUtil.getConnection();
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                m.setId(rs.getInt("id"));
                m.setReplySubject(rs.getInt("replySubject"));
                m.setContent(rs.getString("content"));
                m.setName(rs.getString("name"));
                m.setPhone(rs.getString("phone"));
                m.setEmail(rs.getDate("email").toString());
            }
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  m;
    }

    public int deleteAdviceById(int replySubject,int id)  {

        String sql="DELETE  from egov_advice where replySubject=? and id = ?  ";
        try{
            conn= DruidUtil.getConnection();
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setInt(1,replySubject);
            ps.setInt(2,id);
            if(ps.execute()){
                conn.close();
                return 1;
            }else {
                conn.close();
                return -1;
            }

        }catch (SQLException e){
            e.printStackTrace();
        }

        return  -1;
    }
}
